const {exec, escape} = require("../db/mysql")

// 获取所有教练
const getCoachList = async () => {
  const sql = `select *
               from coach;`
  return await exec(sql)
}
// 添加一名教练
const newCoach = async ({identify, name, sex, phone, years}) => {
  identify = escape(identify)
  name = escape(name)
  sex = escape(sex)
  phone = escape(phone)
  years = escape(years)
  const sql = `insert into coach(identify, name, sex, phone, years, isArranged, progress, isdel)
               values (${identify}, ${name}, ${sex}, ${phone}, ${years}, 0, 0, 0)`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 修改教练信息
const updateCoach = async ({identify, phone, progress}) => {
  identify = escape(identify)
  phone = escape(phone)
  progress = escape(progress)
  let sql = `update coach set `
  if (phone !== 'NULL') {
    sql += `phone = ${phone}`
  }
  if (phone !== 'NULL' && progress !== 'NULL') {
    sql += ', '
  }
  if (progress !== 'NULL') {
    sql += `progress = ${progress}`
  }
  sql += ` where identify = ${identify}`
  console.log(sql)
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 删除教练
const delCoach = async (id) => {
  id = escape(id)
  const sql = `update coach
               set isdel = 1
               where id = ${id};`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}

// 根据身份证号修改教练信息（reverse_car）
const updateCoachUsername = async (identify, reverse_car) => {
  identify = escape(identify)
  reverse_car = escape(reverse_car)
  const sql = `update coach
               set reverse_car = ${reverse_car}
               where identify = ${identify}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 根据身份证号修改教练信息（isArranged）
const updateCoachArrange = async (identify) => {
  identify = escape(identify)
  const sql = `update coach
               set isArranged = 1
               where identify = ${identify}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 取消分配，根据 reverse_car 获取教练信息
const getCoachByCarNumber = async (car_number) => {
  car_number = escape(car_number)
  const sql = `select * from coach where reverse_car = ${car_number}`
  return await exec(sql).then(rows => {
    return rows[0] || {}
  })
}
// 取消预约车辆(取消分配)
const cancelReverseCar = async (username) => {
  username = escape(username)
  const sql = `update coach
               set reverse_car = ''
               where identify = ${username}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 新的一天到来重置教练预约车辆
const resetReverseCar = async () => {
  const sql = `update coach set reverse_car = NULL where reverse_car != '';`
  return await exec(sql).then(data => {
    return true
  })
}

// 获取教练详情
const getCoachDetailByUsername = async (username) => {
  username = escape(username)
  const sql = `select *
               from coach
               where identify = ${username};`
  return await exec(sql).then(rows => {
    return rows[0] || {}
  })
}
// 教练预约车辆
const updateCoachReverseCar = async (username, reverse_car) => {
  username = escape(username)
  reverse_car = escape(reverse_car)
  const sql = `update coach
               set reverse_car = ${reverse_car}
               where identify = ${username}`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}
// 获取教练详细信息
const getCoachDetailById = async (id) => {
  id = escape(id)
  const sql = `select id,
                      identify,
                      name,
                      sex,
                      phone,
                      reverse_car,
                      years,
                      isArranged
               from coach
               where id = ${id};`
  return await exec(sql).then(rows => {
    return rows[0] || {}
  })
}
// 获取角色姓名，编号
const getCoachNameList = async () => {
  const sql = `select id, name
               from coach
               where isdel = 0;`
  return await exec(sql)
}

// 删除教练（管理员）
const delCoachByUsername = async (username) => {
  username = escape(username)
  const sql = `update coach
               set isdel = 1
               where identify = ${username};`
  return await exec(sql).then(data => {
    if (data.affectedRows > 0) {
      return true
    } else {
      return false
    }
  })
}

module.exports = {
  getCoachList,
  getCoachNameList,
  newCoach,
  updateCoach,
  delCoach,
  updateCoachUsername,
  updateCoachArrange,
  getCoachByCarNumber,
  resetReverseCar,
  cancelReverseCar,
  getCoachDetailByUsername,
  getCoachDetailById,
  delCoachByUsername,
  updateCoachReverseCar
}
